package cn.itcast.nems.studentrecord.dao.mapper;

import cn.itcast.nems.studentrecord.dao.dataobject.StudentRecordStudentDO;
import cn.itcast.nems.studentrecord.dao.entity.StudentRecordStudentRelationship;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface StudentRecordStudentRelationshipMapper extends BaseMapper<StudentRecordStudentRelationship> {

    /**
     * 查询改期涉及到的学员(开班日期往前调，如：2日改成1日)
     *
     * @param clazzId      班级ID
     * @param oldStartDate 班级修改后新的开班日期
     * @return 学员
     */
    List<StudentRecordStudentDO> selectChangeDateBeforeInvolveStudents(@Param("clazzId") int clazzId,
                                                                       @Param("oldStartDate") LocalDateTime oldStartDate);

    /**
     * 查询改期涉及到的学员(开班日期往后调，如：1日改成2日)
     *
     * @param clazzId      班级ID
     * @param newStartDate 班级修改后新的开班日期
     * @return 学员
     */
    List<StudentRecordStudentDO> selectChangeDateAfterInvolveStudents(@Param("clazzId") int clazzId,
                                                                      @Param("newStartDate") LocalDateTime newStartDate);

    /**
     * 根据 EMS 学生ID查询 有效的学籍与EMS学生关系
     *
     * @param studentId EMS学生ID
     * @return 学籍与EMS学生关系
     */
    @Select("select * from e2_student_record_student_relationship " +
            "where student_id=#{studentId} and state=1 order by created_datetime desc limit 1")
    StudentRecordStudentRelationship findByStudentId(@Param("studentId") Integer studentId);

    /**
     * 根据 EMS学生ID设置状态为 0
     * @param studentId EMS 学生ID
     * @return 变更条目
     */
    @Update("update e2_student_record_student_relationship set state=0 where student_id =#{studentId}")
    int cancelByStudentId(@Param("studentId") Integer studentId) ;

    /**
     * 查询学籍对应的学籍ID
     *
     * @param studentRecordId 学籍ID
     * @return 学员ID
     */
    Integer selectStudentId(@Param("studentRecordId") String studentRecordId);


    @Select("SELECT \n" +
            "  COUNT(*) \n" +
            "FROM\n" +
            "  `e2_student_record_student_relationship` ship \n" +
            "  LEFT JOIN `e2_student_record` r \n" +
            "    ON r.id = ship.`student_record_id` \n" +
            "WHERE ship.`student_id` = #{studentId}\n" +
            "AND r.`clazz_id` = #{clazzId}\n" +
            "AND r.`state` IN ('VALID', 'PENDING')\n" +
            "AND r.settlement_date IS NULL\n"
    )
    Integer countValid(@Param("studentId") Integer studentId, @Param("clazzId") Integer clazzId);


    @Select("<script>" +
            "\n" +
            "SELECT \n" +
            "  s.`name`\n" +
            "FROM\n" +
            "  `e2_student_record_student_relationship` ship \n" +
            "  LEFT JOIN `e2_student_record` r \n" +
            "    ON r.id = ship.`student_record_id` \n" +
            "    LEFT JOIN student s ON s.id = ship.`student_id`\n" +
            "WHERE \n" +
            "ship.`student_id` IN " +
            "<foreach collection=\"studentIds\" item=\"item\" open=\"(\" separator=\",\" close=\")\">#{item}</foreach>\n" +
            "AND r.`clazz_id` = #{clazzId}\n" +
            "AND r.`state` IN ('VALID', 'PENDING')\n" +
            "AND r.settlement_date IS NULL\n" +
            "</script>")
    List<String> findValidNames(@Param("studentIds") List<Integer> studentIds, @Param("clazzId") Integer clazzId);
}
